#-*- coding:utf-8 -*-
import web, datetime
import web.db
import thread,sys
import MySQLdb
from free_db import free_list
#reload(sys) 
#sys.setdefaultencoding('utf8')

db_name='mssupport'
db_user='ccsdev'
db_passwd = "czacc603+"


db = web.database(
    dbn='mysql',
    host="localhost",
    port=3306,
    user=db_user,
    passwd=db_passwd,
    db=db_name,
    charset="utf8"
)


conn = MySQLdb.connect(host="104.200.21.25",
                       port=3306,
                       user=db_user,
                       passwd=db_passwd,
                       db=db_name,
                       charset="utf8")
#db = web.database(dbn='mysql', db='blog', user='justin')
### 执行sql命令，创建table test
global lock
lock = thread.allocate_lock()

def sql_excute(sql):
    #数据库为dailyfb
    lock.acquire() #
    conn.ping(True)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    result=cursor.fetchall()
    lock.release()
    
    return result

"""
def get_posts(num):
    #取最新的15
    return db.select('news', order='id DESC',limit=int(num))

def get_posts_bysymb(scode,num):
    return db.select('news',where='symb=$scode',order='id DESC', limit=int(num),vars=locals())

def get_posts_byfid(fid,num):
    return db.select('news',where='fid=$fid',order='id DESC', limit=int(num),vars=locals())

def get_post(id):
    try:
        post_content=db.select('news', where='id=$id', vars=locals())[0]
        print post_content
        return post_content
    except IndexError:
        return None

def get_MAXid():
        #返回第一记录的id
        lastest_post=get_posts(1)
        
        return lastest_post[0]['id']

def get_share_MAXid(type):
    try:
        MAXpost=db.select('sharerecord', where='type=$type', limit=1,vars=locals())[0]
        MAXid=MAXpost['MAXid']
    except IndexError:
        MAXid=get_MAXid()-1
    
    return MAXid

def update_share_MAXid(type,id):
    
    db.update('sharerecord', where="type=$type", vars=locals(),MAXid=id)
        
def get_posts_lgtid(id):
    
    #return db.select('news', where='id>$id', order='id DESC', limit=1,vars=locals())
    return db.select('news', where='id>$id', order='id ASC', limit=1,vars=locals())

def get_global_posts(num):
    #取最新的15
    return db.select('entries', order='id DESC',limit=int(num))

def get_global_posts_byfid(fid,num):
    #取最新的15
    #return db.select('entries', order='id DESC',limit=int(num))
    #return db.select('entries',where='fid=$fid',order='id DESC', limit=int(num),vars=locals())
    return db.select('entries',where='fid=$fid',order='id DESC', limit=int(num),vars=locals())

def get_global_post(id):
    #按id浏览单条国际新闻
    try:
        return db.select('entries', where='id=$id', vars=locals())[0]
    except IndexError:
        return None

def get_global_posts_next(id):
    try:
        return db.select('entries', where='id>$id', order='id DESC', limit=8,vars=locals())
    except IndexError:
        return None
   
def get_global_posts_lgtid(id):
    
    #return db.select('entries', where='id>$id', order='id ASC', limit=1,vars=locals())
    return db.query("SELECT * FROM entries WHERE id>$id and fid=501 ORDER BY id DESC LIMIT 1", vars=locals())

def test_only(id):
    return db.query("SELECT * FROM entries WHERE id>$id and fid>=500 ORDER BY id ASC LIMIT 1", vars=locals())
    
def new_post(title, text):
    db.insert('news', title=title, content=text, posted_on=datetime.datetime.utcnow())

def del_post(id):
    db.delete('news', where="id=$id", vars=locals())

def update_post(id, title, text):
    db.update('news', where="id=$id", vars=locals(),
        title=title, content=text) 

######for tsla fans ###########
def get_tsla_news_2share(num,date):
    conn.ping(True)
    return db.query("SELECT * FROM TSLAFANS WHERE shared=0 and pubdate=$date ORDER BY id DESC LIMIT 1",vars=locals() ) 

def update_tsla_shared(id):
    conn.ping(True)
    db.update('TSLAFANS', where="id=$id", vars=locals(),shared=1)

def update_tsla_shared_lastday(lastday):
    conn.ping(True)
    db.update('TSLAFANS',where='pubdate=$lastday', vars=locals(),shared=1)
    
#####end of tsla fans###########

#####start of weibo share list###########


def insert_weibo_need(type,wbids,todaystr):
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(dbname)
    
    #判断标题，如存在则不插入
    sql = "insert ignore into weibo_share_list (type,wbid,date) values (%s,%s,%s)"
    for id in wbids:
        param = (type,#1
                 id,#2
                 todaystr,
                 )
        lock.acquire() 
        cursor.execute(sql,param)
        conn.commit()
        lock.release() 

def update_weibo_status(type,id):
    conn.ping(True)
    db.update('weibo_share_list',where="type=$type and wbid=$id", vars=locals(),shared=1)

def get_weibo_2share(type,num,date):
    conn.ping(True)
    return db.query("SELECT * FROM weibo_share_list WHERE type=$type and shared=0 and date=$date ORDER BY id DESC LIMIT $num",vars=locals())
    

#####endof weibo share list###########

######for ccs_baidu ###########
def get_ccsbaidu_news_2share(num,date):
    conn.ping(True)
    return db.query("SELECT * FROM CCSBAIDU WHERE shared=0 and pubdate=$date ORDER BY id DESC LIMIT 2",vars=locals() ) 

def update_ccsbaidu_shared(id):
    conn.ping(True)
    db.update('CCSBAIDU', where="id=$id", vars=locals(),shared=1)

def update_ccs_shared_lastday(lastday):
    conn.ping(True)
    db.update('CCSBAIDU',where='pubdate=$lastday', vars=locals(),shared=1)
    
#####end of ccs_baidu###########

"""
      
if __name__ == "__main__":
    pass